﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Reflection;
using System.Xml.Linq;
using System.Runtime.Remoting.Messaging;
using RiskAlert_Entity;
using RiskAlert_Tool;


namespace RiskAlert_Repository
{
    public interface IRepository<T>
    {
        void Create(T obj);
        void Delete(string PrimaryKey);
        T Read(string PrimaryKey);
        List<T> ReadLots(string PrimaryKey);
        void Update(T obj, bool isConcat = false);
    }
    public abstract class BaseRepositpory<T> : IRepository<T> where T : class, IPrimaryKey, new()
    {
        protected MySqlConnection _connection;
        protected MySqlTransaction _transaction;
        protected string _tableName;
        protected string _tableKey;
        protected List<string> _concatColumn = new List<string> { "risk_type", "item_desc", "item_tip" };
        public BaseRepositpory(MySqlConnection connection, MySqlTransaction transaction)
        {
            _connection = connection;
            _transaction = transaction;
            _tableName = typeof(T).Name.ToLower();
            _tableKey = $"{_tableName}_id";
        }
        public void Create(T obj)
        {
            List<string> list = new List<string>();
            foreach (var prop in typeof(T).GetProperties())
            {
                string columnName = Tool.PropertyToColumn(prop.Name);
                list.Add(columnName);
            }
            string columns = string.Join(", ", list);
            string parameters = string.Join(", ", list.Select(i => $"@{i}"));
            using (MySqlCommand command = new MySqlCommand($"insert into {_tableName} ({columns}) values ({parameters})", _connection, _transaction))
            {
                int index = 0;
                foreach (var prop in typeof(T).GetProperties())
                {
                    var value = prop.GetValue(obj);
                    command.Parameters.AddWithValue($"@{list[index]}", value);
                    index++;
                }
                command.ExecuteNonQuery();
            }
        }
        public void Delete(string PrimaryKey)
        {
            using (MySqlCommand command = new MySqlCommand($"delete from {_tableName} where {_tableKey} = @PrimaryKey", _connection, _transaction))
            {
                command.Parameters.AddWithValue("@PrimaryKey", PrimaryKey);
                command.ExecuteNonQuery();
            }
        }
        public T Read(string PrimaryKey)
        {
            T obj = new T();
            MySqlDataReader reader = null;
            int index = 0;
            using (MySqlCommand command = new MySqlCommand($"select * from {_tableName} where {_tableKey} = @PrimaryKey", _connection, _transaction))
            {
                command.Parameters.AddWithValue("@PrimaryKey", PrimaryKey);
                reader = command.ExecuteReader();
            }
            if (!reader.HasRows)
            {
                reader.Close();
                return null;
            }
            using (reader)
            {
                while (reader.Read())
                {
                    foreach (var i in typeof(T).GetProperties())
                    {
                        i.SetValue(obj, reader.GetValue(index));
                        index++;
                    }
                }
            }
            return obj;
        }
        public List<T> ReadLots(string PrimaryKey)
        {
            List<T> list = new List<T>();
            MySqlDataReader reader = null;
            using (MySqlCommand command = new MySqlCommand($"select * from {_tableName} where {_tableKey} like concat(@PrimaryKey, '%')", _connection, _transaction))
            {
                command.Parameters.AddWithValue("@PrimaryKey", PrimaryKey);
                reader = command.ExecuteReader();
            }
            using (reader)
            {
                while (reader.Read())
                {
                    int index = 0;
                    T obj = new T();
                    foreach (var i in typeof(T).GetProperties())
                    {
                        i.SetValue(obj, reader.GetValue(index));//========
                        index++;
                    }
                    list.Add(obj);
                }
            }
            if(list.Count > 0)
            {
                return list;
            }
            else return null;
        }
        public void Update(T obj, bool isConcat = false)
        {
            List<string> list = new List<string>();
            foreach(var prop in typeof(T).GetProperties())
            {
                if(prop.Name == obj.GetPrimaryKeyProp()) { continue; }
                var value = prop.GetValue(obj);
                if(value != null && value.ToString() != "")
                {
                    string columnName = Tool.PropertyToColumn(prop.Name);
                    if (_concatColumn.Contains(columnName) && isConcat == true)
                    {
                        list.Add($"{columnName} = concat_ws(',', nullif({columnName}, ''), @{columnName})");
                    }
                    else list.Add($"{columnName} = @{columnName}");
                }
            }
            string updates = string.Join(", ", list);
            using (MySqlCommand command = new MySqlCommand($"update {_tableName} set {updates} where {Tool.PropertyToColumn(obj.GetPrimaryKeyProp())} = @PrimaryKey", _connection, _transaction))
            {
                foreach (var prop in typeof(T).GetProperties())
                {
                    if(prop.Name == obj.GetPrimaryKeyProp()) { continue; }
                    var value = prop.GetValue(obj);
                    if(value != null)
                    {
                        string columnName = Tool.PropertyToColumn(prop.Name);
                        command.Parameters.AddWithValue($"@{columnName}", value);
                    }
                }
                command.Parameters.AddWithValue("@PrimaryKey", obj.GetPrimaryKeyValue());
                command.ExecuteNonQuery();
            }
        }
        

    }
    public class ItemRepository : BaseRepositpory<Item>
    {
        public ItemRepository(MySqlConnection connection, MySqlTransaction transaction) : base(connection, transaction) { }
        public string ReadIdByName(string ItemName)//======================
        {
            using (MySqlCommand command = new MySqlCommand($"select item_id from {_tableName} where item_name = @item_name", _connection, _transaction))
            {
                command.Parameters.AddWithValue("@item_name", ItemName);
                object result = command.ExecuteScalar();
                if (result != null)
                {
                    return result.ToString();
                }
                else return null;
            }
        }

    }
    public class EvidenceRepository : BaseRepositpory<Evidence>
    {
        public EvidenceRepository(MySqlConnection connection, MySqlTransaction transaction) : base(connection,transaction) { }
        public int ReadMax(string ItemId)
        {
            object count = 0;
            using (MySqlCommand command = new MySqlCommand($"select max(cast(substring({_tableKey}, 19, 5) as unsigned)) from {_tableName} where {_tableKey} like concat(@item_id, '%')", _connection, _transaction))
            {
                command.Parameters.AddWithValue("@item_id", ItemId);
                count = command.ExecuteScalar();
            }
            if(count != DBNull.Value)
            {
                return Convert.ToInt32(count);
            }
            else return 0;
        }
        public void DeleteAll(string ItemId)
        {
            using(MySqlCommand command = new MySqlCommand($"delete from {_tableName} where {_tableKey} like concat(@ItemId, '%')" , _connection, _transaction))
            {
                command.Parameters.AddWithValue("@ItemId", ItemId);
                command.ExecuteNonQuery();
            }
        }

    }
    
}
